﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CFG_INTEGRATED_SEARCH_Select')
	BEGIN
		DROP Procedure usp_UPDMS_CFG_INTEGRATED_SEARCH_Select
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CFG_INTEGRATED_SEARCH_Select
**	Desc : 통합검색
**	Test Exec Query : Exec usp_UPDMS_CFG_INTEGRATED_SEARCH_Select 'smstyle','1','ko','후토스','COM,CRD,MNY'
**	Called by : Cfg_Dac_UPDMS_CFG_INTEGRATED_SEARCH.cs
**	Program ID : Cfg2010r
**	Auth : 송시명
**	Date : 2014-01-16
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CFG_INTEGRATED_SEARCH_Select]
@ls_user_id nvarchar(20),
@ls_access_level nvarchar(1),
@ls_lang_set varchar(2),
@ls_keyword nvarchar(max),
@ls_pgm_classes nvarchar(100)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--결과저장
DECLARE @TMP_RESULT TABLE (
  Pgm_Class nvarchar(20),
  Pgm_Id nvarchar(20),
  Seq int,
  Contents nvarchar(max),
  Create_Dt datetime,
  Update_Dt datetime
)

IF LEN(@ls_keyword) > 0
BEGIN

/*
COM - 공통관리
*/
IF CHARINDEX('COM', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--공통코드관리
SELECT 'Com1000m' AS Pgm_Class,
       'Com1010m' AS Pgm_Id, 
       Seq,
       'Id : ' + Id + ', Cd : ' + Cd + ', U_Cd : ' + U_Cd 
	   + ', Value : ' + Value1 + ', ' + Value2 + ', ' + Value3 
	   + ', Attr : ' + Attr1 + ', ' + Attr2 + ', ' + Attr3 AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_COM_CD WITH(NOLOCK)
 WHERE Value1 LIKE '%' + @ls_keyword + '%'
    OR Value2 LIKE '%' + @ls_keyword + '%'
    OR Value3 LIKE '%' + @ls_keyword + '%'
    OR Attr1 LIKE '%' + @ls_keyword + '%'
    OR Attr2 LIKE '%' + @ls_keyword + '%'
    OR Attr3 LIKE '%' + @ls_keyword + '%'
UNION ALL
--프로그램관리
SELECT 'Com1000m' AS Pgm_Class,
       'Com1011m' AS Pgm_Id, 
       Seq,
       'Pgm_Nm : ' + Pgm_Nm + ', Pgm_Nm_Eng : ' + Pgm_Nm_Eng AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_COM_PGM WITH(NOLOCK)
 WHERE Pgm_Nm LIKE '%' + @ls_keyword + '%'
    OR Pgm_Nm_Eng LIKE '%' + @ls_keyword + '%'
UNION ALL
--라벨관리
SELECT 'Com1000m' AS Pgm_Class,
       'Com1017m' AS Pgm_Id, 
       Seq,
       'Label_Ko : ' + Label_Ko + ', Label_En : ' + Label_En + ', Label_Ch : ' + Label_Ch AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_COM_LABEL WITH(NOLOCK)
 WHERE Label_Ko LIKE '%' + @ls_keyword + '%'
    OR Label_En LIKE '%' + @ls_keyword + '%'
    OR Label_Ch LIKE '%' + @ls_keyword + '%'
UNION ALL
--첨부파일
SELECT 'Com1000m' AS Pgm_Class,
       'Com3017p' AS Pgm_Id,
       File_Id,
       'File Nm : ' + File_Nm + CONVERT(VARCHAR, File_Size) + 'kb, width : ' + CONVERT(VARCHAR, File_W_Size) + ', height : ' + CONVERT(VARCHAR, File_H_Size) AS Contents,
       Create_Dt,
       Create_Dt
  FROM UPDMS_COM_FILE WITH(NOLOCK)
 WHERE File_Nm LIKE '%' + @ls_keyword + '%'

END

/*
CRD - 카드관리
*/
IF CHARINDEX('CRD', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--카드기본정보
SELECT 'Crd1000m' AS Pgm_Class,
       'Crd1010m' AS Pgm_Id, 
       Seq,
       'Card_Cd : ' + Card_Cd + ', Card_Nm : ' + Card_Nm + ', Card_Num : ' + Card_Num + ', Cvc_Num : ' + Cvc_Num + 
       ', Valid_Ym : ' + Valid_Ym + ', Accnt_Num : ' + Accnt_Num + ', Attach_File1 : ' + Attach_File1 AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CRD_INFO WITH(NOLOCK)
 WHERE Card_Nm LIKE '%' + @ls_keyword + '%'
    OR Card_Num LIKE '%' + @ls_keyword + '%'
    OR Cvc_Num LIKE '%' + @ls_keyword + '%'
    OR Valid_Ym LIKE '%' + @ls_keyword + '%'
    OR Accnt_Num LIKE '%' + @ls_keyword + '%'
    OR Attach_File1 LIKE '%' + @ls_keyword + '%'
UNION ALL
--카드사용관리
SELECT 'Crd1000m' AS Pgm_Class,
       'Crd1012m' AS Pgm_Id,
       ucib.Seq,
       '[' + uci.Card_Nm + '] ' + Contents + ' (' + ucib.Biz_Dt + ')(' + dbo.ufn_UPDMS_Convert_Money(ucib.Amount) + ') ' + ucib.Remark AS Contents,
       ucib.Create_Dt,
       ucib.Update_Dt
  FROM UPDMS_CRD_ITEM_BIZ ucib WITH(NOLOCK)
  JOIN UPDMS_CRD_INFO uci ON ucib.Card_Cd = uci.Card_Cd
 WHERE ucib.Contents LIKE '%' + @ls_keyword + '%'
    OR ucib.Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--할부사용관리
SELECT 'Crd1000m',
       'Crd1013m',
       ucib.Seq,
       '[' + uci.Card_Nm + '] ' + Contents + ' (' + ucib.Biz_Dt + ')(' + dbo.ufn_UPDMS_Convert_Money(ucib.Amount) + ') ' + ucib.Remark AS Contents,
       ucib.Create_Dt,
       ucib.Update_Dt
  FROM UPDMS_CRD_INSTALLMENT_BIZ ucib WITH(NOLOCK)
  JOIN UPDMS_CRD_INFO uci ON ucib.Card_Cd = uci.Card_Cd
 WHERE ucib.Contents LIKE '%' + @ls_keyword + '%'
    OR ucib.Remark LIKE '%' + @ls_keyword + '%'
    
END

/*
MNY - 현금관리
*/
IF CHARINDEX('MNY', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--은행계좌관리
SELECT 'Mny1000m' AS Pgm_Class,
       'Mny1010m' AS Pgm_Id,
       Seq,
       '[' + dbo.ufn_UPDMS_Get_Code_Name(Bnk_Cd, 'MNY', @ls_lang_set) + ', '
       + dbo.ufn_UPDMS_Get_Code_Name(Accnt_Cd, 'MNY', @ls_lang_set) + ', '
       + Depositor + '] '
       + Accnt_Nm
       + ' (' + Accnt_Num + ') ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_MNY_BNK_ACCNT_INFO WITH(NOLOCK)
 WHERE Accnt_Nm LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
    OR Accnt_Num LIKE '%' + @ls_keyword + '%'
    OR Depositor LIKE '%' + @ls_keyword + '%'
UNION ALL
--현금출납관리
SELECT 'Mny1000m' AS Pgm_Class,
       'Mny1011m' AS Pgm_Id,
       Seq,
       '[' + Reg_Dt + ']'
       + '[' + dbo.ufn_UPDMS_Get_Code_Name(Class, 'MNY', @ls_lang_set) + '-'
       + dbo.ufn_UPDMS_Get_Code_Name(Account, 'MNY', @ls_lang_set) + '] '
       + Contents
       + ' (' + dbo.ufn_UPDMS_Convert_Money(Amount) + ') ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
 WHERE Contents LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'

END

/*
BBY - 육아관리
*/
IF CHARINDEX('BBY', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--수유정보관리
SELECT 'Bby1000m' AS Pgm_Class,
       'Bby1010m' AS Pgm_Id, 
       Seq,
       'Lac_Dt : ' + Lac_Dt + ', Baby_Food_Stuff : ' + Baby_Food_Stuff + ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_BBY_LACTATION_INFO WITH(NOLOCK)
 WHERE Baby_Food_Stuff LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
END

/*
PRJ - 프로젝트관리
*/
IF CHARINDEX('PRJ', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--프로젝트등록
SELECT 'Prj1000m' AS Pgm_Class,
       'Prj1010m' AS Pgm_Id, 
       Seq,
       'Subject : ' + Subject + ', Order_Co : ' + Order_Co + ', Main_Work : ' + Main_Work + ', Sw_Archi : ' + Sw_Archi + 
       ', Hw_Archi : ' + Hw_Archi + ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_PRJ_SUMMARY WITH(NOLOCK)
 WHERE Subject LIKE '%' + @ls_keyword + '%'
    OR Order_Co LIKE '%' + @ls_keyword + '%'
    OR Main_Work LIKE '%' + @ls_keyword + '%'
    OR Sw_Archi LIKE '%' + @ls_keyword + '%'
    OR Hw_Archi LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--프로젝트진척관리
SELECT 'Prj1000m' AS Pgm_Class,
       'Prj1011m' AS Pgm_Id, 
       Seq,
       'Prj_Id : ' + Prj_Id + 'Pgm_Nm : ' + Pgm_Nm + ', Title : ' + Title + ', Contents : ' + Contents + ', Handle_Contents : ' + Handle_Contents + 
       ', Concern_Person : ' + Concern_Person + ', Handle_Time : ' + Handle_Time + ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_PRJ_PROGRESS_MGT WITH(NOLOCK)
 WHERE Pgm_Nm LIKE '%' + @ls_keyword + '%'
    OR Title LIKE '%' + @ls_keyword + '%'
    OR Contents LIKE '%' + @ls_keyword + '%'
    OR Handle_Contents LIKE '%' + @ls_keyword + '%'
    OR Concern_Person LIKE '%' + @ls_keyword + '%'
    OR Handle_Time LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--보고관리
SELECT 'Prj1000m' AS Pgm_Class,
       'Prj1012m' AS Pgm_Id, 
       Seq,
       'Prj_Id : ' + Prj_Id + ', Title : ' + Title + ', Results : ' + Results + ', Plans : ' + Plans AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_PRJ_REPORT_MGT WITH(NOLOCK)
 WHERE Title LIKE '%' + @ls_keyword + '%'
    OR Results LIKE '%' + @ls_keyword + '%'
    OR Plans LIKE '%' + @ls_keyword + '%'
END

/*
CAR - 차량관리
*/
IF CHARINDEX('CAR', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--차량등록
SELECT 'Car1000m' AS Pgm_Class,
       'Car1010m' AS Pgm_Id, 
       Seq,
       'License_Plate : ' + License_Plate + 
       ', Model : ' + Model + 
       ', Prod_Co : ' + Prod_Co +
       ', Color : ' + Color +
       ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CAR_INFO WITH(NOLOCK)
 WHERE License_Plate LIKE '%' + @ls_keyword + '%'
    OR Model LIKE '%' + @ls_keyword + '%'
    OR Prod_Co LIKE '%' + @ls_keyword + '%'
    OR Color LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--주유관리
SELECT 'Car1000m' AS Pgm_Class,
       'Car1011m' AS Pgm_Id, 
       Seq,
       'Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CAR_REFUELLING WITH(NOLOCK)
 WHERE Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--정비관리
SELECT 'Car1000m' AS Pgm_Class,
       'Car1012m' AS Pgm_Id, 
       Seq,
       'Contents : ' + Contents + 
       ', Remark : ' + Remark
       AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
 WHERE Contents LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--차량관련업소관리
SELECT 'Car1000m' AS Pgm_Class,
       'Car1013m' AS Pgm_Id, 
       Seq,
       'Station_Nm : ' + Station_Nm + 
       ', Contact : ' + Contact
       AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CAR_STATION WITH(NOLOCK)
 WHERE Station_Nm LIKE '%' + @ls_keyword + '%'
    OR Contact LIKE '%' + @ls_keyword + '%'
UNION ALL
--정비부품관리
SELECT 'Car1000m' AS Pgm_Class,
       'Car1014m' AS Pgm_Id, 
       Seq,
       'Parts_Nm : ' + Parts_Nm + 
       ', Remark : ' + Remark
       AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CAR_PARTS WITH(NOLOCK)
 WHERE Parts_Nm LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'

END

/*
DVD - 영화관리
*/
IF CHARINDEX('DVD', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--스튜디오관리
SELECT 'Dvd1000m' AS Pgm_Class,
       'Dvd1010m' AS Pgm_Id, 
       Seq,
       'Nm : ' + Nm + 
       ', Website_Url : ' + Website_Url AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_DVD_STUDIO WITH(NOLOCK)
 WHERE Nm LIKE '%' + @ls_keyword + '%'
    OR Website_Url LIKE '%' + @ls_keyword + '%'
UNION ALL
--DVD관리
SELECT 'Dvd1000m' AS Pgm_Class,
       'Dvd1012m' AS Pgm_Id, 
       Seq,
       'Title : ' + Title + 
       ', Dvd_No : ' + Dvd_No +
       ', Actress1 : ' + Actress1 +
       ', Actress2 : ' + Actress2 +
       ', Synopsis : ' + Synopsis AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_DVD_INFO WITH(NOLOCK)
 WHERE Title LIKE '%' + @ls_keyword + '%'
    OR Dvd_No LIKE '%' + @ls_keyword + '%'
    OR Actress1 LIKE '%' + @ls_keyword + '%'
    OR Actress2 LIKE '%' + @ls_keyword + '%'
    OR Synopsis LIKE '%' + @ls_keyword + '%'
END

/*
REF - 힐링관리
*/
IF CHARINDEX('REF', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--업소관리
SELECT 'Ref1000m' AS Pgm_Class,
       'Ref1010m' AS Pgm_Id, 
       Seq,
       'Shop_Nm : ' + Shop_Nm + 
       ', Reserve_Phone : ' + Reserve_Phone +
       ', Address : ' + [Address] +
       ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_REF_SHOP WITH(NOLOCK)
 WHERE Shop_Nm LIKE '%' + @ls_keyword + '%'
    OR Reserve_Phone LIKE '%' + @ls_keyword + '%'
    OR [Address] LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--기행기관리
SELECT 'Ref1000m' AS Pgm_Class,
       'Ref1012m' AS Pgm_Id, 
       Seq,
       'Nick_Nm : ' + Nick_Nm + 
       ', Place : ' + Place +
       ', Contents : ' + Contents +
       ', Keyword : ' + Keyword AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_REF_TOUR WITH(NOLOCK)
 WHERE Nick_Nm LIKE '%' + @ls_keyword + '%'
    OR Place LIKE '%' + @ls_keyword + '%'
    OR Contents LIKE '%' + @ls_keyword + '%'
    OR Keyword LIKE '%' + @ls_keyword + '%'
END

/*
FIT - 건강관리
*/
IF CHARINDEX('FIT', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--운동종류관리
SELECT 'Fit1000m' AS Pgm_Class,
       'Fit1010m' AS Pgm_Id, 
       Seq,
       'Training_Nm : ' + Training_Nm + 
       ', Training_Muscle_Major : ' + Training_Muscle_Major + 
       ', Training_Muscle_Minor : ' + Training_Muscle_Minor +
       ', Howto : ' + Howto +
       ', Precaution : ' + Precaution +
       ', Tip : ' + Tip AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_FIT_TRAINING_TYPE WITH(NOLOCK)
 WHERE Training_Nm LIKE '%' + @ls_keyword + '%'
    OR Training_Muscle_Major LIKE '%' + @ls_keyword + '%'
    OR Training_Muscle_Minor LIKE '%' + @ls_keyword + '%'
    OR Howto LIKE '%' + @ls_keyword + '%'
    OR Precaution LIKE '%' + @ls_keyword + '%'
    OR Tip LIKE '%' + @ls_keyword + '%'
UNION ALL
--트레이닝관리
SELECT 'Fit1000m' AS Pgm_Class,
       'Fit1011m' AS Pgm_Id, 
       Seq,
       'Location : ' + Location AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_FIT_WEIGHT_TRAINING WITH(NOLOCK)
 WHERE Location LIKE '%' + @ls_keyword + '%'
UNION ALL
--신체지수관리
SELECT 'Fit1000m' AS Pgm_Class,
       'Fit1012m' AS Pgm_Id, 
       Seq,
       'Memo : ' + Memo AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
 WHERE Memo LIKE '%' + @ls_keyword + '%'

END

/*
ETC - 기타관리
*/
IF CHARINDEX('ETC', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--주소록관리
SELECT 'Etc1000m' AS Pgm_Class,
       'Etc1010m' AS Pgm_Id, 
       Seq,
       'Nm : ' + Nm + 
       ', Cell_Phone : ' + Cell_Phone +
       ', Home_Phone : ' + Home_Phone +
       ', Email : ' + Email +
       ', Zip_Cd : ' + Zip_Cd +
       ', Address : ' + [Address] +
       ', Memo : ' + Memo AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_ETC_PERSON_MGT WITH(NOLOCK)
 WHERE Nm LIKE '%' + @ls_keyword + '%'
    OR Cell_Phone LIKE '%' + @ls_keyword + '%'
    OR Home_Phone LIKE '%' + @ls_keyword + '%'
    OR Email LIKE '%' + @ls_keyword + '%'
    OR Zip_Cd LIKE '%' + @ls_keyword + '%'
    OR [Address] LIKE '%' + @ls_keyword + '%'
    OR Memo LIKE '%' + @ls_keyword + '%'
UNION ALL
--웹사이트관리
SELECT 'Etc1000m' AS Pgm_Class,
       'Etc1011m' AS Pgm_Id, 
       Seq,
       'Nm : ' + Nm + 
       ', Url : ' + Url +
       ', Id : ' + Id +
       ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_ETC_WEBSITE WITH(NOLOCK)
 WHERE Nm LIKE '%' + @ls_keyword + '%'
    OR Url LIKE '%' + @ls_keyword + '%'
    OR Id LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'
UNION ALL
--통합게시판
SELECT 'Etc1000m' AS Pgm_Class,
       'Etc1012m' AS Pgm_Id, 
       Seq,
       'Title : ' + Title + 
       ', Contents : ' + Contents AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_ETC_BOARD WITH(NOLOCK)
 WHERE Title LIKE '%' + @ls_keyword + '%'
    OR Contents LIKE '%' + @ls_keyword + '%'
UNION ALL
--구글캘린더
SELECT 'Etc1000m' AS Pgm_Class,
       'Etc1013m' AS Pgm_Id, 
       Seq,
       'Title : ' + Title + 
       ', Contents : ' + Contents +
       ', Location : ' + Location
       AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_ETC_PLANNER WITH(NOLOCK)
 WHERE Title LIKE '%' + @ls_keyword + '%'
    OR Contents LIKE '%' + @ls_keyword + '%'
    OR Location LIKE '%' + @ls_keyword + '%'
UNION ALL
--레고관리
SELECT 'Etc1000m' AS Pgm_Class,
       'Etc1014m' AS Pgm_Id, 
       Seq,
       'Prod_Nm : ' + Prod_Nm + 
       ', Contents : ' + Contents +
       ', Keyword : ' + Keyword
       AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_ETC_LEGO_MGT WITH(NOLOCK)
 WHERE Prod_Nm LIKE '%' + @ls_keyword + '%'
    OR Contents LIKE '%' + @ls_keyword + '%'
    OR Keyword LIKE '%' + @ls_keyword + '%'
END

/*
ETC - 구성관리
*/
IF CHARINDEX('CFG', @ls_pgm_classes) > 0
BEGIN

INSERT @TMP_RESULT
--시스템관리
SELECT 'Cfg1000m' AS Pgm_Class,
       'Cfg1010m' AS Pgm_Id, 
       Seq,
       'Cmd_Nm : ' + Cmd_Nm + 
       ', Cmd_File_Nm : ' + Cmd_File_Nm +
       ', Cmd_Contents : ' + Cmd_Contents AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CFG_BATCH_MGT WITH(NOLOCK)
 WHERE Cmd_Nm LIKE '%' + @ls_keyword + '%'
    OR Cmd_File_Nm LIKE '%' + @ls_keyword + '%'
    OR Cmd_Contents LIKE '%' + @ls_keyword + '%'
UNION ALL
--설정관리
SELECT 'Cfg1000m' AS Pgm_Class,
       'Cfg1011m' AS Pgm_Id, 
       Seq,
       'Setting_Nm : ' + Setting_Nm + 
       ', Setting_Cd : ' + Setting_Cd +
       ', Setting_Value : ' + Setting_Value +
       ', Remark : ' + Remark AS Contents,
       Create_Dt,
       Update_Dt
  FROM UPDMS_CFG_SETTING_MGT WITH(NOLOCK)
 WHERE Setting_Nm LIKE '%' + @ls_keyword + '%'
    OR Setting_Cd LIKE '%' + @ls_keyword + '%'
    OR Setting_Value LIKE '%' + @ls_keyword + '%'
    OR Remark LIKE '%' + @ls_keyword + '%'

END

--결과리턴
SELECT y.Pgm_Class,
       CASE @ls_lang_set WHEN 'ko' THEN y.Pgm_Nm ELSE y.Pgm_Nm_Eng END Pgm_Class_Nm,
       x.Pgm_Id,
	   CASE @ls_lang_set WHEN 'ko' THEN x.Pgm_Nm ELSE x.Pgm_Nm_Eng END Pgm_Nm,
       x.Seq,
       x.Contents,
       x.Create_Dt,
       x.Update_Dt
  FROM (
       SELECT tr.Pgm_Class,
              tr.Pgm_Id,
              ucp.Pgm_Nm,
			  ucp.Pgm_Nm_Eng,
              tr.Seq,
              tr.Contents,
              tr.Create_Dt,
              tr.Update_Dt
         FROM @TMP_RESULT tr
         JOIN UPDMS_COM_PGM ucp WITH(NOLOCK) ON tr.Pgm_Id = ucp.Pgm_Id
      ) x
  JOIN UPDMS_COM_PGM y WITH(NOLOCK) ON x.Pgm_Class = y.Pgm_Id AND y.Pgm_Div = 'D'
 ORDER BY Pgm_Class, Pgm_Id

END

GO